﻿Imports System
Imports System.IO

Module MOD_Database

    Public unamDB As String
    Public DBserv As String
    Public DBtabl As String
    Public DBuser As String
    Public DBpass As String
    Public DBport As String
    Public DBsec As String
    Public DATAB As Boolean
    Public BasePath As String
    Public Base_Name As String
    Public DB_N As String

    Public strPassword As String, Temp As String
    Public SUBD As String

    Public DB7 As ADODB.Connection
    Public ConNect As String

    Public Function LogDBErrors()
        Dim Errs1 As Errors
        Dim i As Integer
        Dim errLoop As ADODB.Error
        Dim strTmp As String
        Dim ado_err_no As Long

        i = 1
        On Error Resume Next

        ' Enumerate Errors collection and display properties of
        ' each Error object (if Errors Collection is filled out)

        Errs1 = DB7.Errors()
        For Each errLoop In Errs1
            With errLoop
                strTmp = strTmp & vbCrLf & "ADO Error # " & i & ":"
                ado_err_no = .Number
                If ado_err_no <> -2147467259 Then
                    strTmp = strTmp & vbCrLf & "   ADO Error   # " & ado_err_no
                End If
                strTmp = strTmp & vbCrLf & "   Description   " & .Description
                strTmp = strTmp & vbCrLf & "   Source        " & .Source
                strTmp = strTmp & vbCrLf & "   SqlState      " & .SQLState
                strTmp = strTmp & vbCrLf & "   NativeError   " & .NativeError
                i = i + 1
            End With
        Next
        If (strTmp <> "") Then
            MsgBox(strTmp)
        Else
            MsgBox("Undetected error!")
        End If

    End Function



    Public Function GENID() As Long

        Randomize()
        GENID = Int(Rnd() * 1000000000)


        'Dim numbers As Integer() = Enumerable.Range(1, 1000000000).ToArray()

    End Function

    Public Sub TestDatabaseConnection(ByVal DBserv As String, ByVal DBport As String, ByVal DBname As String, ByVal DBuser As String, ByVal DBpass As String)

        Dim uname As String
        Dim sfile As String
        Try
            sfile = Base_Name

            unamDB = ConnectSetup.cmbSUBD.Text

            If Len(unamDB) = 0 Or unamDB = Nothing Then
                MsgBox("Select database type!")
                Exit Sub
            End If

            If unamDB = "MS Access" And Len(sfile) = 0 Then
                MsgBox("No file name for Access DB selected!")
                Exit Sub
            End If

            Dim MyShadowPassword As String
            Dim TempStr As String

            sfile = Base_Name

            MyShadowPassword = ""

            DB7 = New ADODB.Connection

            Select Case unamDB
                Case "MS SQL 2008"
                    DB7.Open("Driver={SQL Server Native Client 10.0};Server=" & DBserv & ";Database=" & DBname & ";Uid=" & DBuser & ";Pwd=" & DBpass & ";")
                Case "MS SQL"
                    DB7.Open("Driver={SQL Server};Server=" & DBserv & ";Database=" & DBname & ";Uid=" & DBuser & ";Pwd=" & DBpass & ";")
                Case "MS Access"
                    DB7.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & BasePath & "\" & sfile & ";Jet OLEDB:Database Password=" & MyShadowPassword & ";")
                Case "MySQL (MyODBC 3.51)"
                    DB7.Open("Driver={MySQL ODBC 3.51 Driver};Server=" & DBserv & ";Port=" & DBport & ";Database=" & DBname & ";User=" & DBuser & "; Password=" & DBpass & ";OPTION=2059; -С;")
                Case "MySQL"
                    DB7.Open("Driver={MySQL ODBC 5.1 Driver};Server=" & DBserv & ";Database=" & DBname & ";Port=" & DBport & ";User=" & DBuser & "; Password=" & DBpass & ";")
                Case "PostgreSQL"
                    DB7.Open("Driver={PostgreSQL Unicode};Server=" & DBserv & ";Port=" & DBport & ";Database=" & DBname & ";Uid=" & DBuser & ";Pwd=" & DBpass & ";")
                Case "SQLLite"
                    DB7.Open("DRIVER=SQLite3 ODBC Driver;Database=" & BasePath & "\" & sfile & ";LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;")
                Case "DSN"
                    DB7.Open("DSN=" & DBserv & ";UID=" & DBuser & ";PWD=" & DBpass & ";")
            End Select

            MsgBox("All OK! Connection successfull!")

            Exit Sub
        Catch ex As Exception
            LogDBErrors()
        End Try
    End Sub

    Public Sub LoadDatabase(Optional ByRef sFile As String = "")
        Dim uname As String
        Try
            sFile = Base_Name

            ' unamDB = ConnectSetup.cmbSUBD.Text

            If Len(unamDB) = 0 Or unamDB = Nothing Then
                MsgBox("Select database type!")
                Exit Sub
            End If

            If unamDB = "MS Access" And Len(sFile) = 0 Then
                MsgBox("No file name for Access DB selected!")
                Exit Sub
            End If

            Dim MyShadowPassword As String
            Dim TempStr As String

            DATAB = True
            sFile = Base_Name

            MyShadowPassword = ""


            If Len(unamDB) = 0 Then
                unamDB = "MS Access"
            Else
            End If
            DB7 = New ADODB.Connection

            Select Case unamDB

                Case "MS SQL 2008"

                    DB7.Open("Driver={SQL Server Native Client 10.0};Server=" & DBserv & ";Database=" & DBtabl & ";Uid=" & DBuser & ";Pwd=" & DBpass & ";")


                Case "MS SQL"

                    '2000
                    DB7.Open("Driver={SQL Server};Server=" & DBserv & ";Database=" & DBtabl & ";Uid=" & DBuser & ";Pwd=" & DBpass & ";")




                Case "MS Access"
                    DB7.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & BasePath & "\" & sFile & ";Jet OLEDB:Database Password=" & MyShadowPassword & ";")


                Case "MySQL (MyODBC 3.51)"

                    DB7.Open("Driver={MySQL ODBC 3.51 Driver};Server=" & DBserv & ";Port=" & DBport & ";Database=" & DBtabl & ";User=" & DBuser & "; Password=" & DBpass & ";OPTION=2059; -С;")

                    'Driver={MySQL ODBC 3.51 Driver};Server=myServerAddress;charset=UTF8;Database=myDataBase;User=myUsername; Password=myPassword;Option=3;
                    'DB7.Open("Driver={MySQL ODBC 5.1 Driver};Server=" & DBserv & ";Port=" & DBport & ";Database=" & DBtabl & ";User=" & DBuser & "; Password=" & DBpass & ";OPTION=2059;")
                    'DB7.Open("DSN=BKO2;Server=" & DBserv & ";Port=" & DBport & ";Database=" & DBtabl & ";Uid=" & DBuser & ";Pwd=" & DBpass & ";")

                Case "MySQL"

                    DB7.Open("Driver={MySQL ODBC 5.1 Driver};Server=" & DBserv & ";Database=" & DBtabl & ";Port=" & DBport & ";User=" & DBuser & "; Password=" & DBpass & ";")
                    'DB7.Open("Driver={MySQL ODBC 5.1 Driver};Server=" & DBserv & ";Database=" & DBtabl & ";Port=" & DBport & ";User=" & DBuser & "; Password=" & DBpass & ";Option=2059;-С;")

                Case "PostgreSQL"

                    DB7.Open("Driver={PostgreSQL Unicode};Server=" & DBserv & ";Port=" & DBport & ";Database=" & DBtabl & ";Uid=" & DBuser & ";Pwd=" & DBpass & ";")

                    'DB7.Open("Driver={PostgreSQL};Server=" & DBserv & ";Port=" & DBport & ";Database=" & DBtabl & ";Uid=" & DBuser & ";Pwd=" & DBpass & ";")

                    'DB7.Open("DSN=PGS" & ";UID=" & DBuser & ";PWD=" & DBpass & ";" & "Database=" & DBtabl & ";")

                Case "SQLLite"

                    DB7.Open("DRIVER=SQLite3 ODBC Driver;Database=" & BasePath & "\" & sFile & ";LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;")


                Case "DSN"
                    DB7.Open("DSN=" & DBserv & ";UID=" & DBuser & ";PWD=" & DBpass & ";")



            End Select

            DB_N = unamDB

            Exit Sub
        Catch ex As Exception
            DATAB = False
            LogDBErrors()
        End Try

    End Sub

    Public Sub UnLoadDatabase(Optional ByRef sFile As String = "")
        Try
            If Len(unamDB) = 0 Or unamDB = Nothing Then Exit Sub
            'If unamDB = "MS Access" And Len(sFile) = 0 Then Exit Sub
            If DATAB = False Then Exit Sub

            DB7.Close()
            DB7 = Nothing

            DATAB = False
        Catch ex As Exception
            LogDBErrors()
        End Try
    End Sub


    Public Sub EncryptDecrypt(ByVal Strpassword As String)
        Dim i, Code, intLocation

        Code = "1234567890" 'This is key for encrypting/decrypting
        Temp = ""
        For i = 1 To Len(Strpassword)
            intLocation = (i Mod Len(Code)) + 1
            'Use XOR logic combination for encrypting/decrypting
            Temp = Temp + Chr(Asc(Mid(Strpassword, i, 1)) Xor Asc(Mid(Code, intLocation, 1)))
        Next
    End Sub
End Module
